<?php
class PersonneManager {
    public function __construct(Mypdo $db){
        $this->db= $db;
    }
	
	public function add(Personne $personne){
		$req = $this->db->prepare('INSERT INTO immoloc.personne (  nom_pers, prenom_pers, num_fix_pers,
		 num_port_pers, email_pers,genre_pers, num_ad_pers, voie_ad_pers, cp_ad_pers, ville_ad_pers )
		 VALUES (:nom_pers, :prenom_pers, :num_fix_pers, :num_port_pers, :email_pers, :genre_pers, :num_ad_pers, :voie_ad_pers, :cp_ad_pers, :ville_ad_pers)');
		$req->bindValue(':nom_pers', $personne->getNomPers(), PDO::PARAM_STR);
		$req->bindValue(':prenom_pers', $personne->getPrenomPers(), PDO::PARAM_STR);
		$req->bindValue(':num_fix_pers', $personne->getNumFixPers(), PDO::PARAM_STR);
		$req->bindValue(':num_port_pers', $personne->getNumPortPers(), PDO::PARAM_STR);
		$req->bindValue(':email_pers', $personne->getEmailPers(), PDO::PARAM_STR);
		$req->bindValue(':genre_pers', $personne->getGenrePers(), PDO::PARAM_STR);

        $req->bindValue(':num_ad_pers', $personne->getAdressePers()->getNumAdresse(), PDO::PARAM_INT);
        $req->bindValue(':voie_ad_pers', $personne->getAdressePers()->getVoieAdresse(), PDO::PARAM_STR);
        $req->bindValue(':cp_ad_pers', $personne->getAdressePers()->getCpAdresse(), PDO::PARAM_STR);
        $req->bindValue(':ville_ad_pers', $personne->getAdressePers()->getVilleAdresse(), PDO::PARAM_STR);


		$req->execute();

        return $this->db->lastInsertId();

	}
	
	public function getList(){
		$listePersonnes = array();
		$sql = 'SELECT id_pers, nom_pers, prenom_pers, num_fix_pers, num_port_pers, email_pers, genre_pers FROM personne ORDER BY id_pers DESC';
		$req = $this->db->query($sql);
		while ($personne = $req->fetch(PDO::FETCH_OBJ)){
			$listePersonnes[] = new Personne ($personne);
		}
        $req->closeCursor();
		return $listePersonnes;

	}
	
	public function getNbPers(){
		$listePersonnes = $this->getList();
		$nbPers = 0;
		foreach($listePersonnes as $personne){
			$nbPers++;
		}
		return $nbPers;
	}
	
	public function getPers($id_pers){
		$req = $this->db->prepare('SELECT *
									FROM personne 
									WHERE id_pers = :id_pers');
		$req->bindValue(':id_pers', $id_pers, PDO::PARAM_INT);
		
		$req->execute();
        $res = $req->fetch(PDO::FETCH_ASSOC);
		$pers = new Personne($res);
        $pers->setAdressePers(new Adresse($res));
		$req->closeCursor();
		return $pers;
	}

    public function getId(Personne $personne)
    {

        $sql = 'SELECT id_pers FROM immoloc.personne WHERE nom_pers = :nom_pers AND prenom_pers = :prenom_pers AND email_pers = :email_pers AND genre_pers = :genre_pers';
        $req = $this->db->prepare($sql);
        $req->bindValue(':nom_pers', $personne->getNomPers(), PDO::PARAM_STR);
        $req->bindValue(':prenom_pers', $personne->getPrenomPers(), PDO::PARAM_STR);
        $req->bindValue(':email_pers', $personne->getEmailPers(), PDO::PARAM_STR);
        $req->bindValue(':genre_pers', $personne->getGenrePers(), PDO::PARAM_STR);

        $req->execute();
        return $req->fetch(PDO::FETCH_OBJ)->id_pers;
    }

    public function delPers($id_pers){
        $sql = 'DELETE FROM immoloc.personne WHERE id_pers = :id_pers';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_pers', $id_pers, PDO::PARAM_INT);

        $req->execute();
    }

    public function  delPersByIdPropri($id_propri){
        $sql = '
        SELECT DISTINCT id_pers FROM partage p
        INNER JOIN locataire la ON la.id_loca = p.id_loca
        INNER JOIN occupe o ON la.id_loca = o.id_loca
        INNER JOIN location l ON o.id_loc = l.id_loc
        INNER JOIN immeuble i ON l.id_immeu = i.id_immeu
        WHERE i.id_propri = :id_propri';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_propri', $id_propri, PDO::PARAM_STR);

        $req->execute();

        $table = $req->fetchAll(PDO::FETCH_ASSOC);

        $partManager =new PartageManager($this->db);
        foreach ($table as $value) {
            $partManager->delPartByIdPers($value['id_pers']);
        }

        foreach ($table as $value) {
            $this->delPers($value['id_pers']);
        }


    }

    public function  delPersByIdImmeu($id_immeu){
        $sql = '
        SELECT DISTINCT id_pers FROM partage p
        INNER JOIN locataire la ON la.id_loca = p.id_loca
        INNER JOIN occupe o ON la.id_loca = o.id_loca
        INNER JOIN location l ON o.id_loc = l.id_loc
        WHERE l.id_immeu = :id_immeu';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_immeu', $id_immeu, PDO::PARAM_STR);

        $req->execute();

        $table = $req->fetchAll(PDO::FETCH_ASSOC);

        $partManager =new PartageManager($this->db);
        foreach ($table as $value) {
            $partManager->delPartByIdPers($value['id_pers']);
        }

        foreach ($table as $value) {
            $this->delPers($value['id_pers']);
        }


    }

    public function delPersByIdLoc($id_loc)
    {
        $sql = '
        SELECT DISTINCT id_pers FROM partage p
        INNER JOIN locataire la ON la.id_loca = p.id_loca
        INNER JOIN occupe o ON la.id_loca = o.id_loca
        WHERE o.id_loc = :id_loc';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loc', $id_loc, PDO::PARAM_STR);

        $req->execute();

        $table = $req->fetchAll(PDO::FETCH_ASSOC);

        $partManager =new PartageManager($this->db);
        foreach ($table as $value) {
            $partManager->delPartByIdPers($value['id_pers']);
        }

        foreach ($table as $value) {
            $this->delPers($value['id_pers']);
        }

    }

    public function delPersByIdLoca($id_loca)
    {
        $sql = '
        SELECT DISTINCT id_pers FROM partage p
        WHERE id_loca = :id_loca';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_STR);

        $req->execute();

        $table = $req->fetchAll(PDO::FETCH_ASSOC);

        $partManager =new PartageManager($this->db);
        foreach ($table as $value) {
            $partManager->delPartByIdPers($value['id_pers']);
        }

        foreach ($table as $value) {
            $this->delPers($value['id_pers']);
        }
    }

    public function getListNouv($id_loca)
    {
        $sql = 'SELECT * FROM personne WHERE id_pers NOT IN
        (SELECT id_pers FROM partage WHERE id_loca = :id_loca )';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_STR);

        $req->execute();

        $table = $req->fetchAll(PDO::FETCH_ASSOC);

        $liste = array();
        foreach ($table as $value) {
            $liste[] = new Personne($value);
        }

        return $liste;
    }

    public function update(Personne $pers)
    {
        $req = $this->db->prepare('UPDATE personne SET nom_pers = :nom_pers, prenom_pers = :prenom_pers, num_fix_pers = :num_fix_pers,
                                      num_port_pers = :num_port_pers, email_pers = :email_pers, genre_pers = :genre_pers,
                   num_ad_pers = :num_ad_pers, voie_ad_pers = :voie_ad_pers, cp_ad_pers = :cp_ad_pers, ville_ad_pers = :ville_ad_pers
                   WHERE id_pers = :id_pers');

        $req->bindValue(':id_pers', $pers->getIdPers(), PDO::PARAM_INT);

        $req->bindValue(':nom_pers', $pers->getNomPers(), PDO::PARAM_STR);
        $req->bindValue(':prenom_pers', $pers->getPrenomPers(), PDO::PARAM_STR);
        $req->bindValue(':num_fix_pers', $pers->getNumFixPers(), PDO::PARAM_STR);
        $req->bindValue(':num_port_pers', $pers->getNumPortPers(), PDO::PARAM_STR);
        $req->bindValue(':email_pers', $pers->getEmailPers(), PDO::PARAM_STR);
        $req->bindValue(':genre_pers', $pers->getGenrePers(), PDO::PARAM_STR);



        $req->bindValue(':num_ad_pers', $pers->getAdressePers()->getNumAdresse(), PDO::PARAM_INT);
        $req->bindValue(':voie_ad_pers', $pers->getAdressePers()->getVoieAdresse(), PDO::PARAM_STR);
        $req->bindValue(':cp_ad_pers', $pers->getAdressePers()->getCpAdresse(), PDO::PARAM_STR);

        $req->bindValue(':ville_ad_pers', $pers->getAdressePers()->getVilleAdresse(), PDO::PARAM_STR);


        $req->execute();

    }

}
?>